﻿-- =============================================
-- Script Template
-- =============================================
USE [DBMySQLTill]
GO

/****** Object:  StoredProcedure [dbo].[Till_MYSQL_get_tills_data]    Script Date: 12/05/2011 10:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Till_MYSQL_get_tills_data]
(@date varchar(20), @server varchar(100))
AS
BEGIN
    declare @conStr varchar(MAX);
	SET NOCOUNT ON;

set @conStr =	'Execute(''select MID(tis.name,7) as "Магазин",
ROUND(Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=1))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=1)),0) as "Касса 1",
ROUND(Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=2))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=2)),0) as "Касса 2",
ROUND(Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=3))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=4)),0) as  "Касса 3",
ROUND(Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=4))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=4)),0) as  "Касса 4",
ROUND(Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=1))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=1))+Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=2))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=2))+Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=3))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=3))+Sum(CONVERT(p.amount, DECIMAL(7,2))*(h.type=0)*(h.pos=4))-Sum(CONVERT(p.amount, DECIMAL(7,2))*
(h.type=1 or h.type=4)*(h.pos=4)),0) as "Итого"
from trm_out_receipt_header h left join trm_out_receipt_payment p ON (p.cash_id=h.cash_id)and
(p.receipt_header=h.id) left join trm_out_receipt_footer f on (f.id = h.id and f.cash_id = h.cash_id)
inner join trm_in_pos tip on tip.cash_id=h.cash_id and tip.deleted=0 and tip.active=1
inner join trm_in_store tis ON tis.store_id=tip.store_id
 where f.result=0 and p.type in (0) and (h.type=0 or h.type=1 or h.type=4) and h.date> "'+@date+'" and 
 h.date < adddate( "'+@date+'",INTERVAL 1 DAY) group by tis.store_id ORDER BY MID(tis.name,7)'') at ' + @server;
 
Execute(@conStr);

END

GO


